﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SQLite;
using Models;

namespace DAL
{
    public class CustomerManager
    {
        /// <summary>
        /// 获取所有顾客信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllCustomerInfoDataSet()
        {
            string sql = "select * from tb_customerInfo";
            return SQLHelper.GetDataSet(sql, "allCustomerInfo");
        }





        /// <summary>
        /// 获取对应房间入住顾客信息
        /// </summary>
        /// <param name="roomNum">房号</param>
        /// <returns>返回入住顾客信息list</returns>
        public List<Customer> GetCustomerInfo(string roomNum)
        {
            string sql = string.Format("select 身份证号,姓名,性别,入住房号,入住时间,预住天数,押金 from tb_customerInfo where tb_customerInfo.入住房号='{0}'", roomNum);
            SQLiteDataReader rd = SQLHelper.GetReader(sql);
            List<Customer> list = new List<Customer>();
            while (rd.Read())
            {
                list.Add(new Customer { 
                    IDCardNum=rd["身份证号"].ToString(),
                    Name=rd["姓名"].ToString(),
                    Sex=rd["性别"].ToString(),
                    RoomNum=rd["入住房号"].ToString(),
                    BookedTime=rd["入住时间"].ToString(),
                    BookedDay=Convert.ToInt32(rd["预住天数"]),
                    Deposit=Convert.ToDouble(rd["押金"]),
                });
            }
            return list;
        }

        /// <summary>
        /// 通过房号获取顾客信息
        /// </summary>
        /// <param name="roomNum"></param>
        /// <returns></returns>
        public DataSet GetCustomerInfoByRoomNum(string roomNum)
        {
            string sql = string.Format("select * from tb_customerInfo where 入住房号='{0}'", roomNum);
            return SQLHelper.GetDataSet(sql, "customerInfoByRoomNum");
        }


        /// <summary>
        /// 通过身份证号获取顾客信息
        /// </summary>
        /// <param name="idcard"></param>
        /// <returns></returns>
        public DataSet GetCustomerInfoByIDCard(string idcard)
        {
            string sql = string.Format("select * from tb_customerInfo where 身份证号='{0}'", idcard);
            return SQLHelper.GetDataSet(sql, "customerInfoByIDCard");
        }


        /// <summary>
        /// 通过姓名获取顾客信息
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public DataSet GetCustomerInfoByName(string name)
        {
            string sql = string.Format("select * from tb_customerInfo where 姓名 like '%{0}%'", name);
            return SQLHelper.GetDataSet(sql, "customerInfoByName");
        }


        /// <summary>
        /// 开房 插入顾客信息
        /// </summary>
        /// <param name="customer">顾客对象</param>
        /// <returns></returns>
        public int AddRecord(Customer customer)
        {
            string sql = string.Format("insert into tb_customerInfo (身份证号,姓名,性别,入住房号,入住时间,预住天数,押金,备注) values('{0}','{1}','{2}','{3}','{4}',{5},{6},'{7}')",customer.IDCardNum, customer.Name, customer.Sex, customer.RoomNum, customer.BookedTime, customer.BookedDay, customer.Deposit, customer.Remarks);
            return SQLHelper.Update(sql);
        }


        /// <summary>
        /// 删除顾客记录（退房时）
        /// </summary>
        /// <param name="iDCardNum">身份证号</param>
        /// <returns></returns>
        public int DelRecord(string iDCardNum)
        {
            string sql = string.Format("delete from tb_customerInfo where 身份证号='{0}'", iDCardNum);
            return SQLHelper.Update(sql);
        }



        /// <summary>
        /// 开房时插入顾客历史记录
        /// </summary>
        /// <param name="customerHistory">顾客历史对象</param>
        /// <returns></returns>
        public int AddHistoryRecord(CustomerHistory customerHistory)
        {
            string sql = string.Format("insert into tb_customerHistory (身份证号,姓名,性别,入住房号,开始时间,结束时间,备注) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", customerHistory.IDCardNum, customerHistory.Name, customerHistory.Sex, customerHistory.RoomNum, customerHistory.StartTime, customerHistory.EndTime, customerHistory.Remarks);
            return SQLHelper.Update(sql);
        }

        /// <summary>
        /// 更新顾客历史信息（结束时间、消费金额）
        /// </summary>
        /// <param name="endTime">结束时间</param>
        /// <param name="consumptionAmount">消费金额</param>
        /// <param name="iDCardNum">身份证号</param>
        /// <returns></returns>
        public int UpdateCustomerHistory(string endTime,double consumptionAmount,string iDCardNum)
        {
            string sql = string.Format("update tb_customerHistory set 结束时间='{0}',消费金额={1} where 身份证号='{2}'", endTime, consumptionAmount, iDCardNum);
            return SQLHelper.Update(sql);
        }


        /// <summary>
        /// 获取全部顾客历史信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllCustomerHistoryDataSet()
        {
            string sql = "select * from tb_customerHistory";
            return SQLHelper.GetDataSet(sql, "allCustomerHistory");
        }


        /// <summary>
        /// 通过房号获取顾客历史信息
        /// </summary>
        /// <param name="roomNum"></param>
        /// <returns></returns>
        public DataSet GetCustomerHistoryByRoomNum(string roomNum)
        {
            string sql = string.Format("select * from tb_customerHistory where 入住房号='{0}'", roomNum);
            return SQLHelper.GetDataSet(sql, "customerHistoryByRoomNum");
        }


        /// <summary>
        /// 通过身份证号获取顾客历史信息
        /// </summary>
        /// <param name="idcard"></param>
        /// <returns></returns>
        public DataSet GetCustomerHistoryByIDCard(string idcard)
        {
            string sql = string.Format("select * from tb_customerHistory where 身份证号='{0}'", idcard);
            return SQLHelper.GetDataSet(sql, "customerHistoryByIDCard");
        }


        /// <summary>
        /// 通过姓名获取顾客历史信息
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public DataSet GetCustomerHistoryByName(string name)
        {
            string sql = string.Format("select * from tb_customerHistory where 姓名 like '%{0}%'", name);
            return SQLHelper.GetDataSet(sql, "customerHistoryByName");
        }
    }
}
